package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bean.DormitoryBean;
import bean.FileBean;

public class FileDao {
	public List<List<FileBean>> getFileList(int id) throws SQLException, ClassNotFoundException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = conn.prepareStatement("select dormitoryid, dormitoryname from dormitory ,((select dormitoryid1 from friend_dormitory where dormitoryid2=?) union all (select dormitoryid2 from friend_dormitory where dormitoryid1=?))u where dormitory.dormitoryid=u.dormitoryid1");
		ps.setInt(1, id);
		ps.setInt(2, id);
		ResultSet result = ps.executeQuery();
		List<Integer> dormitoryIdList = new ArrayList<>();
		while(result.next()) {
			dormitoryIdList.add(result.getInt(1));
		}
		result.close();
		ps.close();

		ps = conn.prepareStatement("select * from file where dormitoryid=?");
		PreparedStatement ps2 = conn.prepareStatement("select dormitoryname from dormitory where dormitoryid=?");
		ResultSet result2;
		int l = dormitoryIdList.size();
		List<List<FileBean>> list = new ArrayList<>();
		List<FileBean> fList;
		FileBean file;
		
		ps.setInt(1, id);
		result = ps.executeQuery();
		ps2.setInt(1, id);
		result2 = ps2.executeQuery();
		result2.next();
		fList = new ArrayList<>();
		file = new FileBean();
		file.setDormitory(result2.getString(1));
		fList.add(file);
		while(result.next()) {
			file = new FileBean();
			file.setId(result.getInt(1));
			file.setFileName(result.getString(2));
			file.setUserName(result.getString(3));
			file.setDormitory(result2.getString(1));
			file.setIsSharing(result.getBoolean(6));
			file.setDormitoryId(id);
			fList.add(file);
		}
		list.add(fList);
		result.close();
		result2.close();
		ps.close();
		ps = conn.prepareStatement("select * from file where dormitoryid=? and issharing=1");
		
		for(int i=0;i<l;i++) {
			ps.setInt(1, dormitoryIdList.get(i));
			ps2.setInt(1, dormitoryIdList.get(i));
			result = ps.executeQuery();
			result2 = ps2.executeQuery();
			result2.next();
			fList = new ArrayList<>();
			file = new FileBean();
			file.setDormitory(result2.getString(1));
			fList.add(file);
			while(result.next()) {
				file = new FileBean();
				file.setId(result.getInt(1));
				file.setFileName(result.getString(2));
				file.setUserName(result.getString(3));
				file.setDormitory(result2.getString(1));
				file.setIsSharing(result.getBoolean(6));
				file.setDormitoryId(dormitoryIdList.get(i));
				fList.add(file);
			}
			list.add(fList);
			result.close();
			result2.close();
		}
		
		ps.close();
		ps2.close();
		conn.close();
		return list;
	}
	public void insertFile(FileBean file) throws ClassNotFoundException, SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = conn.prepareStatement("insert into file(filename, username, path, dormitoryid, issharing) values(?, ?, ?, ?, ?)");
		ps.setString(1, file.getFileName());
		ps.setString(2, file.getUserName());
		ps.setString(3, file.getPath());
		ps.setInt(4, file.getDormitoryId());
		ps.setBoolean(5, file.getIsSharing());
		ps.executeUpdate();
		ps.close();
		conn.close();
	}
	public String getFilePath(int id) throws SQLException, ClassNotFoundException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = conn.prepareStatement("select path from file where fileid=?");
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		String path = null;
		while(rs.next()) {
			path = rs.getString(1);
		}
		rs.close();
		ps.close();
		conn.close();
		return path;
	}
}
